package com.lmscn.lms.api.model;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Model;

import java.util.List;


/**
 * Created by quxingyu on 2015/8/6.
 */
public class TestpaperResult extends Model<TestpaperResult> {
    public static final TestpaperResult dao = new TestpaperResult();
    
    /**
          * 查询已考次数
     * type yearexam
     * @param studentId
     * @param id
     * @param type
     * @return
     */
    public Long getTestpaperCount(Long studentId, Long examId, String type, int isFinish, int isMakeup) {
    	String sql = "select count(id) from t_testpaper_result where student_id = " + studentId;
    	if (isFinish==1) {
    		sql += " and status = 'finished'";
    	} else if (isFinish==0) {
    		sql += " and status != 'finished'";
    	}
    	if (type!=null) {
    		sql += " and target_type = '" + type + "'";
    	}
    	if (examId!=null) {
    		sql += " and target_id = " + examId;
    	}
    	if (isMakeup!=-1) {  // 补考
    		sql += " and is_makeup = " + isMakeup;
    	}
    	
    	return Db.queryLong(sql);
    }
    
    /**
          * 通过学员ID、考试ID、考试类型查看是否有未完成的考试
     * @param  studentId 学员ID
     * @param id    考试id或测试ID
     * @param type  类型  分为courseexam  coursetest  faceclazzexam faceclazztest
     * */
    public Long getMaxScore(Long studentId, Long id, String type, int isFinish, int isMakeup) {
    	String sql = "select max(score) from t_testpaper_result where student_id = " + studentId;
    	if (isFinish==1) {
    		sql += " and status = 'finished'";
    	} else if (isFinish==0) {
    		sql += " and status != 'finished'";
    	}
    	if (type!=null) {
    		sql += " and target_type = '" + type + "'";
    	}
    	if (id!=null) {
    		sql += " and target_id = " + id;
    	}
    	if (isMakeup!=-1) {  // 补考
    		sql += " and is_makeup = " + isMakeup;
    	}
    	
    	return Db.queryLong(sql);
    }
    
    /**
     * 通过学员ID、考试ID、考试类型查看是否有未完成的考试
     * @param  studentId 学员ID
     * @param id    考试id或测试ID
     * @param type  类型  分为courseexam  coursetest  faceclazzexam faceclazztest
     * */
    public List<TestpaperResult> getTestpaperResult(Long studentId, Long examId,
    		String type, int isFinish, int isMakeup, String year) {
    	String sql = "select * from t_testpaper_result where student_id = " + studentId;
    	if (isFinish==1) {
    		sql += " and status = 'finished'";
    	} else if (isFinish==0) {
    		sql += " and status != 'finished'";
    	}
    	if (type!=null) {
    		sql += " and target_type = '" + type + "'";
    	}
    	if (examId!=null) {
    		sql += " and target_id = " + examId;
    	}
    	if (isMakeup!=-1) {  // 补考
    		sql += " and is_makeup = " + isMakeup;
    	}
    	if (year!=null) {
    		sql += " and target_id in (select id from t_exam where year = "+ year +")";
    	}
    	
    	return dao.find(sql);
    }

    /**
     * 查询答卷结果
     * @param studentId
     * @param testpaperId
     * @return
     */
    public TestpaperResult getByStuIdAndTestpaperId(Long studentId, Long testpaperId){
        return dao.findFirst("select * from t_testpaper_result where student_id = ? and testpaper_id = ?", studentId, testpaperId);
    }

    /**
     * 查询最大分值的记录
     * @param studentId
     * @param targetId
     * @param type
     * @return
     */
    public TestpaperResult findMaxScore(Long studentId, Long targetId, String type){
        return dao.findFirst("select a.score,a.used_time,a.testpaper_id from t_testpaper_result a " +
                "where a.score=(select MAX(s.score) as score from t_testpaper_result s  " +
                "where s.student_id = ? and s.target_id = ? and s.target_type = ? and s.status = 'finished') ", studentId, targetId, type);
    }

    public TestpaperResult findBytestPaperId(Long testperId){
        return dao.findFirst("select t.* from t_testpaper_result t where t.testpaper_id =?",testperId);
    }
    
    /**
          * 通过学员ID、考试ID、考试类型查看是否有未完成的考试
     * @param  studentId 学员ID
     * @param id    考试id或测试ID
     * @param type  类型  分为courseexam  coursetest  faceclazzexam faceclazztest
     * @param isMakeup 0未补考   1补考
     * */
    public TestpaperResult getFinishedTestpaperResult(Long studentId, Long targetId,
    		String targetType, int isFinish, int isMakeup) {
    	String sql = "select * from t_testpaper_result where student_id = " + studentId;
    	if (isFinish==1) {
    		sql += " and status = 'finished'";
    	}
    	if (targetType!=null) {
    		sql += " and target_type = '" + targetType + "'";
    	}
    	if (targetId>0) {
    		sql += " and target_id = " + targetId;
    	}
    	if (isMakeup!=-1) {  // 补考
    		sql += " and is_makeup = " + isMakeup;
    	}
    	sql += " order by score desc";
    	
        return dao.findFirst(sql);
    }
    
}
